from typing import List, Dict, Any, Optional, Literal
from datetime import datetime
import sqlite3
from pydantic import BaseModel, Field, field_validator
from app.db.database import db

class LessonBase(BaseModel):
    """Lesson基础模型"""
    lessonId: str = Field(..., description="LessonID，格式：Lesson-20251010-v1")
    source: str = Field(..., description="来源")
    process: str = Field(..., description="失效工序")
    factory: str = Field(..., description="工厂")
    projectName: str = Field(..., description="项目名称")
    occurenceTime: str = Field(..., description="发生时间，格式：2025-10-10 14:20")
    problemType: str = Field(..., description="问题类型")
    faReport: str = Field(..., description="FA系统报告")
    problemDescription: str = Field(..., description="问题描述")
    improvement: str = Field(..., description="改善措施")
    creator: str = Field(..., description="创建人")
    updater: Optional[str] = Field(None, description="更新人")
    
    @field_validator('lessonId')
    @classmethod
    def validate_lesson_id(cls, v):
        if not v.startswith('Lesson-'):
            raise ValueError('LessonID必须以Lesson-开头')
        return v

class LessonCreate(LessonBase):
    """创建Lesson模型"""
    pass

class LessonUpdate(BaseModel):
    """更新Lesson模型"""
    lessonId: Optional[str] = Field(None, description="LessonID")
    source: Optional[str] = Field(None, description="来源")
    process: Optional[str] = Field(None, description="失效工序")
    factory: Optional[str] = Field(None, description="工厂")
    projectName: Optional[str] = Field(None, description="项目名称")
    occurenceTime: Optional[str] = Field(None, description="发生时间")
    problemType: Optional[str] = Field(None, description="问题类型")
    faReport: Optional[str] = Field(None, description="FA系统报告")
    problemDescription: Optional[str] = Field(None, description="问题描述")
    improvement: Optional[str] = Field(None, description="改善措施")
    updater: Optional[str] = Field(None, description="更新人")

class LessonResponse(LessonBase):
    """Lesson响应模型"""
    id: int
    createTime: Optional[str] = Field(None, description="创建时间")
    updateTime: Optional[str] = Field(None, description="最近更新时间")
    
    class Config:
        from_attributes = True

class LessonWithLearnsResponse(LessonResponse):
    """包含关联学习课程的Lesson响应模型"""
    learns: List[dict] = Field(default_factory=list, description="关联的学习课程列表")

class Lesson:
    """课程模型类"""
    
    def __init__(self, lessonId: str, source: str, process: str, factory: str,
                 projectName: str, occurenceTime: str, problemType: str,
                 faReport: str, problemDescription: str, improvement: str,
                 creator: str, updater: str = None, lesson_id: int = None):
        """
        初始化课程
        """
        self.id = lesson_id
        self.lessonId = lessonId
        self.source = source
        self.process = process
        self.factory = factory
        self.projectName = projectName
        self.occurenceTime = occurenceTime
        self.problemType = problemType
        self.faReport = faReport
        self.problemDescription = problemDescription
        self.improvement = improvement
        self.creator = creator
        self.updater = updater
        self.createTime = None
        self.updateTime = None
    
    def save(self) -> int:
        """
        保存课程到数据库
        :return: 新插入记录的ID或现有记录的ID
        """
        if self.id:
            # 更新现有记录
            query = """
                UPDATE lesson 
                SET lessonId = ?, source = ?, process = ?, factory = ?,
                    projectName = ?, occurenceTime = ?, problemType = ?,
                    faReport = ?, problemDescription = ?, improvement = ?,
                    updater = ?, updateTime = CURRENT_TIMESTAMP
                WHERE id = ?
            """
            params = (self.lessonId, self.source, self.process, self.factory,
                     self.projectName, self.occurenceTime, self.problemType,
                     self.faReport, self.problemDescription, self.improvement,
                     self.updater, self.id)
            db.execute_update(query, params)
            return self.id
        else:
            # 插入新记录
            query = """
                INSERT INTO lesson (lessonId, source, process, factory, projectName,
                    occurenceTime, problemType, faReport, problemDescription,
                    improvement, creator, updater)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """
            params = (self.lessonId, self.source, self.process, self.factory,
                     self.projectName, self.occurenceTime, self.problemType,
                     self.faReport, self.problemDescription, self.improvement,
                     self.creator, self.updater)
            self.id = db.execute_insert(query, params)
            return self.id
    
    def delete(self) -> bool:
        """
        从数据库中删除课程
        :return: 是否删除成功
        """
        if not self.id:
            return False
        
        # 删除关联的学习课程关系
        db.execute_update("DELETE FROM learn_lesson WHERE lesson_id = ?", (self.id,))
        
        # 删除课程
        return db.delete_by_id("lesson", self.id)
    
    def add_to_learn(self, learn_id: int) -> bool:
        """
        添加到学习课程
        :param learn_id: 学习课程ID
        :return: 是否添加成功
        """
        if not self.id:
            return False
        
        try:
            query = "INSERT INTO learn_lesson (learn_id, lesson_id) VALUES (?, ?)"
            db.execute_update(query, (learn_id, self.id))
            return True
        except sqlite3.IntegrityError:
            # 关系已存在
            return False
    
    def remove_from_learn(self, learn_id: int) -> bool:
        """
        从学习课程中移除
        :param learn_id: 学习课程ID
        :return: 是否移除成功
        """
        if not self.id:
            return False
        
        query = "DELETE FROM learn_lesson WHERE learn_id = ? AND lesson_id = ?"
        affected_rows = db.execute_update(query, (learn_id, self.id))
        return affected_rows > 0
    
    def get_learns(self) -> List[Dict[str, Any]]:
        """
        获取关联的所有学习课程
        :return: 学习课程列表
        """
        if not self.id:
            return []
        
        query = """
            SELECT l.* FROM learn l
            INNER JOIN learn_lesson ll ON l.id = ll.learn_id
            WHERE ll.lesson_id = ?
            ORDER BY l.learnId
        """
        return db.execute_query(query, (self.id,))
    
    def to_dict(self) -> Dict[str, Any]:
        """
        转换为字典格式
        :return: 字典格式的数据
        """
        return {
            'id': self.id,
            'lessonId': self.lessonId,
            'source': self.source,
            'process': self.process,
            'factory': self.factory,
            'projectName': self.projectName,
            'occurenceTime': self.occurenceTime,
            'problemType': self.problemType,
            'faReport': self.faReport,
            'problemDescription': self.problemDescription,
            'improvement': self.improvement,
            'creator': self.creator,
            'updater': self.updater,
            'createTime': self.createTime,
            'updateTime': self.updateTime
        }
    
    @classmethod
    def get_by_id(cls, lesson_id: int) -> Optional['Lesson']:
        """
        根据ID获取课程
        :param lesson_id: 课程ID
        :return: Lesson实例或None
        """
        data = db.get_by_id("lesson", lesson_id)
        if not data:
            return None
        
        lesson = cls(
            lessonId=data['lessonId'],
            source=data['source'],
            process=data['process'],
            factory=data['factory'],
            projectName=data['projectName'],
            occurenceTime=data['occurenceTime'],
            problemType=data['problemType'],
            faReport=data['faReport'],
            problemDescription=data['problemDescription'],
            improvement=data['improvement'],
            creator=data['creator'],
            updater=data['updater'],
            lesson_id=data['id']
        )
        lesson.createTime = data['createTime']
        lesson.updateTime = data['updateTime']
        return lesson
    
    @classmethod
    def get_by_lesson_id(cls, lesson_id: str) -> Optional['Lesson']:
        """
        根据LessonID获取课程
        :param lesson_id: LessonID字符串
        :return: Lesson实例或None
        """
        query = "SELECT * FROM lesson WHERE lessonId = ?"
        results = db.execute_query(query, (lesson_id,))
        if not results:
            return None
        
        data = results[0]
        lesson = cls(
            lessonId=data['lessonId'],
            source=data['source'],
            process=data['process'],
            factory=data['factory'],
            projectName=data['projectName'],
            occurenceTime=data['occurenceTime'],
            problemType=data['problemType'],
            faReport=data['faReport'],
            problemDescription=data['problemDescription'],
            improvement=data['improvement'],
            creator=data['creator'],
            updater=data['updater'],
            lesson_id=data['id']
        )
        lesson.createTime = data['createTime']
        lesson.updateTime = data['updateTime']
        return lesson
    
    @classmethod
    def get_all(cls, limit: int = None, offset: int = 0) -> List['Lesson']:
        """
        获取所有课程
        :param limit: 限制数量
        :param offset: 偏移量
        :return: Lesson实例列表
        """
        query = "SELECT * FROM lesson ORDER BY lessonId"
        
        if limit:
            query += f" LIMIT {limit} OFFSET {offset}"
        
        data_list = db.execute_query(query)
        lessons = []
        
        for data in data_list:
            lesson = cls(
                lessonId=data['lessonId'],
                source=data['source'],
                process=data['process'],
                factory=data['factory'],
                projectName=data['projectName'],
                occurenceTime=data['occurenceTime'],
                problemType=data['problemType'],
                faReport=data['faReport'],
                problemDescription=data['problemDescription'],
                improvement=data['improvement'],
                creator=data['creator'],
                updater=data['updater'],
                lesson_id=data['id']
            )
            lesson.createTime = data['createTime']
            lesson.updateTime = data['updateTime']
            lessons.append(lesson)
        
        return lessons
    
    @classmethod
    def search_by_lesson_id(cls, lesson_id: str) -> List['Lesson']:
        """
        根据LessonID搜索课程
        :param lesson_id: 搜索关键词
        :return: Lesson实例列表
        """
        query = "SELECT * FROM lesson WHERE lessonId LIKE ? ORDER BY lessonId"
        params = (f"%{lesson_id}%",)
        data_list = db.execute_query(query, params)
        
        lessons = []
        for data in data_list:
            lesson = cls(
                lessonId=data['lessonId'],
                source=data['source'],
                process=data['process'],
                factory=data['factory'],
                projectName=data['projectName'],
                occurenceTime=data['occurenceTime'],
                problemType=data['problemType'],
                faReport=data['faReport'],
                problemDescription=data['problemDescription'],
                improvement=data['improvement'],
                creator=data['creator'],
                updater=data['updater'],
                lesson_id=data['id']
            )
            lesson.createTime = data['createTime']
            lesson.updateTime = data['updateTime']
            lessons.append(lesson)
        
        return lessons
    
    @classmethod
    def get_by_factory(cls, factory: str) -> List['Lesson']:
        """
        根据工厂获取课程
        :param factory: 工厂名称
        :return: Lesson实例列表
        """
        query = "SELECT * FROM lesson WHERE factory = ? ORDER BY lessonId"
        params = (factory,)
        data_list = db.execute_query(query, params)
        
        lessons = []
        for data in data_list:
            lesson = cls(
                lessonId=data['lessonId'],
                source=data['source'],
                process=data['process'],
                factory=data['factory'],
                projectName=data['projectName'],
                occurenceTime=data['occurenceTime'],
                problemType=data['problemType'],
                faReport=data['faReport'],
                problemDescription=data['problemDescription'],
                improvement=data['improvement'],
                creator=data['creator'],
                updater=data['updater'],
                lesson_id=data['id']
            )
            lesson.createTime = data['createTime']
            lesson.updateTime = data['updateTime']
            lessons.append(lesson)
        
        return lessons
    
    @classmethod
    def get_count(cls) -> int:
        """
        获取课程总数
        :return: 总数
        """
        query = "SELECT COUNT(*) as count FROM lesson"
        result = db.execute_query(query)
        return result[0]['count'] if result else 0
    
    def __str__(self):
        return f"Lesson(id={self.id}, lessonId='{self.lessonId}', factory='{self.factory}')"
    
    def __repr__(self):
        return self.__str__()
